﻿using System;
using System.IO;
using System.Web;
using NPOI.HSSF.UserModel;

namespace TemplateDemo
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        public static void ExportDataTableToExcel(MemoryStream memoryStream, string fileName)
        {
            HttpResponse response = HttpContext.Current.Response;
            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
            response.Clear();

            response.BinaryWrite(memoryStream.GetBuffer());
            response.End();
        }

        protected void DownloadReport_Click(object sender, EventArgs e)
        {
            FileStream fs = new FileStream(Server.MapPath(@"\template\Template_EventBudget.xls"), FileMode.Open, FileAccess.Read);

            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

            HSSFSheet sheet = templateWorkbook.GetSheet("Event Budget");

            sheet.GetRow(1).GetCell(1).SetCellValue(EventName.Value);
            sheet.GetRow(1).GetCell(5).SetCellValue(DateTime.Parse(EventDate.Value));

            sheet.GetRow(5).GetCell(2).SetCellValue(Double.Parse(Roomandhallfees.Value));
            sheet.GetRow(6).GetCell(2).SetCellValue(Double.Parse(Sitestaff.Value));
            sheet.GetRow(7).GetCell(2).SetCellValue(Double.Parse(Equipment.Value));
            sheet.GetRow(8).GetCell(2).SetCellValue(Double.Parse(Tablesandchairs.Value));
            sheet.GetRow(12).GetCell(2).SetCellValue(Double.Parse(Flowers.Value));
            sheet.GetRow(13).GetCell(2).SetCellValue(Double.Parse(Candles.Value));
            sheet.GetRow(14).GetCell(2).SetCellValue(Double.Parse(Lighting.Value));
            sheet.GetRow(15).GetCell(2).SetCellValue(Double.Parse(Balloons.Value));
            sheet.GetRow(16).GetCell(2).SetCellValue(Double.Parse(Papersupplies.Value));
            sheet.GetRow(20).GetCell(2).SetCellValue(Double.Parse(Graphicswork.Value));
            sheet.GetRow(21).GetCell(2).SetCellValue(Double.Parse(Photocopying_Printing.Value));
            sheet.GetRow(22).GetCell(2).SetCellValue(Double.Parse(Postage.Value));
            sheet.GetRow(26).GetCell(2).SetCellValue(Double.Parse(Telephone.Value));
            sheet.GetRow(27).GetCell(2).SetCellValue(Double.Parse(Transportation.Value));
            sheet.GetRow(28).GetCell(2).SetCellValue(Double.Parse(Stationerysupplies.Value));
            sheet.GetRow(29).GetCell(2).SetCellValue(Double.Parse(Faxservices.Value));
            sheet.GetRow(33).GetCell(2).SetCellValue(Double.Parse(Food.Value));
            sheet.GetRow(34).GetCell(2).SetCellValue(Double.Parse(Drinks.Value));
            sheet.GetRow(35).GetCell(2).SetCellValue(Double.Parse(Linens.Value));
            sheet.GetRow(36).GetCell(2).SetCellValue(Double.Parse(Staffandgratuities.Value));
            sheet.GetRow(40).GetCell(2).SetCellValue(Double.Parse(Performers.Value));
            sheet.GetRow(41).GetCell(2).SetCellValue(Double.Parse(Speakers.Value));
            sheet.GetRow(42).GetCell(2).SetCellValue(Double.Parse(Travel.Value));
            sheet.GetRow(43).GetCell(2).SetCellValue(Double.Parse(Hotel.Value));
            sheet.GetRow(44).GetCell(2).SetCellValue(Double.Parse(Other.Value));
            sheet.GetRow(48).GetCell(2).SetCellValue(Double.Parse(Ribbons_Plaques_Trophies.Value));
            sheet.GetRow(49).GetCell(2).SetCellValue(Double.Parse(Gifts.Value));

            sheet.ForceFormulaRecalculation = true;

            MemoryStream ms = new MemoryStream();

            templateWorkbook.Write(ms);

            ExportDataTableToExcel(ms, "EventExpenseReport.xls");
        }

    }
}
